{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians - Easy\n",
    "\n",
    "## Tables used in the musicians database\n",
    "\n",
    "band(**band_no**, band_name, band_home, band_type, b_date, band_contact)\n",
    "\n",
    "composer(**comp_no**, comp_is, comp_type)\n",
    "\n",
    "composition(**c_no**, comp_date, c_title, c_in)\n",
    "\n",
    "concert(**concert_no**, concert_venue, concert_in, con_date, concert_orgniser)\n",
    "\n",
    "has_composed(**_cmpr_no_**, **_cmpn_no_**)\n",
    "\n",
    "musician(**m_no**, m_name, born, died, born_in, living_in)\n",
    "\n",
    "performance(**_pfrmnc_no_**, gave, performed, conducted_by, performed_in)\n",
    "\n",
    "performer(**perf_no**, perf_is, instrument, perf_type)\n",
    "\n",
    "place(**place_no**, place_town, place_country)\n",
    "\n",
    "plays_in(**_player_**, **band_id**)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **musician**\n",
    "\n",
    "m_no | m_name | born | died | born_in | living_in\n",
    "-:|-----------|------|------|--------:|-----:\n",
    "1 | Fred Bloggs | 02/01/48 |  | 1 | 2\n",
    "2 | John Smith | 03/03/50 |  | 3 | 4\n",
    "3 | Helen Smyth | 08/08/48 |  | 4 | 5\n",
    "4 | Harriet Smithson | 09/05/1909 | 20/09/80 | 5 | 6\n",
    "5 | James First | 10/06/65 |  | 7 | 7\n",
    "... | | | | | \n",
    "\n",
    "- **place**\n",
    "\n",
    "place_no | place_town | place_country\n",
    "--------:|------------|---------\n",
    "1 | Manchester | England\n",
    "2 | Edinburgh | Scotland\n",
    "3 | Salzburg | Austria\n",
    "4 | New York | USA\n",
    "5 | Birmingham | England\n",
    "... | | \n",
    "\n",
    "- **performer**\n",
    "\n",
    "perf_no | perf_is | instrument | perf_type\n",
    "-:|--:|--------|---------------------\n",
    "1 | 2 | violin | classical\n",
    "2 | 4 | viola | classical\n",
    "3 | 6 | banjo | jazz\n",
    "4 | 8 | violin | classical\n",
    "5 | 12 | guitar | jazz\n",
    "...| | |\n",
    "\n",
    "- **composer**\n",
    "\n",
    "comp_no | comp_is | comp_type\n",
    "-:|--:|----------------\n",
    "1 | 1 | jazz\n",
    "2 | 3 | classical\n",
    "3 | 5 | jazz\n",
    "4 | 7 | classical\n",
    "5 | 9 | jazz\n",
    "... | |\n",
    "\n",
    "- **band**\n",
    "\n",
    "band_no | band_name | band_home | band_type | b_date | band_contact\n",
    "-:|-----|--:|-----------|----------|----:\n",
    "1 | ROP | 5 | classical | 30/01/01 | 11\n",
    "2 | AASO | 6 | classical |  | 10\n",
    "3 | The J Bs | 8 | jazz |  | 12\n",
    "4 | BBSO | 9 | classical |  | 21\n",
    "5 | The left Overs | 2 | jazz |  | 8\n",
    "... | | | | |\n",
    "\n",
    "- **plays_in**\n",
    "\n",
    "player | band_id\n",
    "-:|---:\n",
    "1 | 1\n",
    "1 | 7\n",
    "3 | 1\n",
    "4 | 1\n",
    "4 | 7\n",
    "... |\n",
    "\n",
    "- **composition**\n",
    "\n",
    "c_no | comp_date | c_title | c_in\n",
    "-:|----------|--------|-------:\n",
    "1 | 17/06/75 | Opus 1 | 1\n",
    "2 | 21/07/76 | Here Goes | 2\n",
    "3 | 14/12/81 | Valiant Knight | 3\n",
    "4 | 12/01/82 | Little Piece | 4\n",
    "5 | 13/03/85 | Simple Song | 5\n",
    "... | | |\n",
    "\n",
    "- **has_composed**\n",
    "\n",
    "cmpr_no | cmpn_no\n",
    "-:|---:\n",
    "1 | 1\n",
    "1 | 8\n",
    "2 | 11\n",
    "3 | 2\n",
    "3 | 13\n",
    "... |\n",
    "\n",
    "- **concert**\n",
    "\n",
    "concert_no | concert_venue | concert_in | con_date | concert_orgniser\n",
    "-:|------------------|--:|----------|----:\n",
    "1 | Bridgewater Hall | 1 | 06/01/95 | 21\n",
    "2 | Bridgewater Hall | 1 | 08/05/96 | 3\n",
    "3 | Usher Hall | 2 | 03/06/95 | 3\n",
    "4 | Assembly Rooms | 2 | 20/09/97 | 21\n",
    "5 | Festspiel Haus | 3 | 21/02/95 | 8\n",
    "... | | | |\n",
    "\n",
    "- **performance**\n",
    "\n",
    "pfrmnc_no | gave | performed | conducted_by | performed_in\n",
    "-:|--:|--:|---:|-----------------------:\n",
    "1 | 1 | 1 | 21 | 1\n",
    "2 | 1 | 3 | 21 | 1\n",
    "3 | 1 | 5 | 21 | 1\n",
    "4 | 1 | 2 | 1 | 2\n",
    "5 | 2 | 4 | 21 | 2\n",
    "... | | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is the data model of the musician tables\n",
    "\n",
    "![](../src/img/musician_str.svg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading spark-stubs, spark-hive\n",
      "Adding Hive conf dir /opt/hive/conf to classpath\n",
      "Creating SparkSession\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "SLF4J: No SLF4J providers were found.\n",
      "SLF4J: Defaulting to no-operation (NOP) logger implementation\n",
      "SLF4J: See https://www.slf4j.org/codes.html#noProviders for further details.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<a target=\"_blank\" href=\"http://jupyter:4040\">Spark UI</a>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "\u001b[32mimport \u001b[39m\u001b[36m$ivy.$                                  \n",
       "\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.log4j.{Level, Logger}\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark.sql._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark.sql.types._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark.sql.functions._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36morg.apache.spark.sql.expressions.Window\n",
       "\n",
       "\u001b[39m\n",
       "\u001b[36mspark\u001b[39m: \u001b[32mSparkSession\u001b[39m = org.apache.spark.sql.SparkSession@46c0cc87"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import $ivy.`org.apache.spark::spark-sql:3.4.0`\n",
    "\n",
    "import org.apache.log4j.{Level, Logger}\n",
    "Logger.getLogger(\"org\").setLevel(Level.OFF)\n",
    "\n",
    "import org.apache.spark._\n",
    "import org.apache.spark.sql._\n",
    "import org.apache.spark.sql.types._\n",
    "import org.apache.spark.sql.functions._\n",
    "import org.apache.spark.sql.expressions.Window\n",
    "\n",
    "val spark = {\n",
    "    NotebookSparkSession.builder()\n",
    "    .progress(false)\n",
    "    .appName(\"app16-1\")\n",
    "    // .master(\"spark://192.168.31.31:7077\")\n",
    "    .master(\"local[*]\")\n",
    "    .config(\"spark.sql.warehouse.dir\", \n",
    "            \"hdfs://192.168.31.31:9000/user/hive/warehouse\") \n",
    "    .config(\"spark.cores.max\", \"4\") \n",
    "    .config(\"spark.executor.instances\", \"1\") \n",
    "    .config(\"spark.executor.cores\", \"2\") \n",
    "    .config(\"spark.executor.memory\", \"10g\") \n",
    "    .config(\"spark.shuffle.service.enabled\", \"false\") \n",
    "    .config(\"spark.dynamicAllocation.enabled\", \"false\") \n",
    "    .config(\"spark.sql.catalogImplementation\", \"hive\")\n",
    "    .config(\"spark.sql.repl.eagerEval.enabled\", \"true\")\n",
    "    .config(\"spark.driver.allowMultipleContexts\", \"true\")\n",
    "    .getOrCreate()\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[32mimport \u001b[39m\u001b[36mspark.implicits._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36mspark.sqlContext.implicits._\n",
       "\u001b[39m\n",
       "defined \u001b[32mfunction\u001b[39m \u001b[36msc\u001b[39m\n",
       "\u001b[36mhiveCxt\u001b[39m: \u001b[32msql\u001b[39m.\u001b[32mhive\u001b[39m.\u001b[32mHiveContext\u001b[39m = org.apache.spark.sql.hive.HiveContext@74c4f249"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import spark.implicits._\n",
    "import spark.sqlContext.implicits._\n",
    "def sc = spark.sparkContext\n",
    "val hiveCxt = new org.apache.spark.sql.hive.HiveContext(sc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defined \u001b[32mclass\u001b[39m \u001b[36mRichDF\u001b[39m"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// Credit to Aivean\n",
    "implicit class RichDF(val ds:DataFrame) {\n",
    "    def showHTML(limit: Int = 50, truncate: Int = 100) = {\n",
    "        import xml.Utility.escape\n",
    "        val data = ds.take(limit)\n",
    "        val header = ds.schema.fieldNames.toSeq        \n",
    "        val rows: Seq[Seq[String]] = data.map { row =>\n",
    "          row.toSeq.map {cell =>\n",
    "            val str = cell match {\n",
    "              case null => \"null\"\n",
    "              case binary: Array[Byte] => binary.map(\"%02X\".format(_)).mkString(\"[\", \" \", \"]\")\n",
    "              case array: Array[_] => array.mkString(\"[\", \", \", \"]\")\n",
    "              case seq: Seq[_] => seq.mkString(\"[\", \", \", \"]\")\n",
    "              case _ => cell.toString\n",
    "            }\n",
    "            if (truncate > 0 && str.length > truncate) {\n",
    "              // do not show ellipses for strings shorter than 4 characters.\n",
    "              if (truncate < 4) str.substring(0, truncate)\n",
    "              else str.substring(0, truncate - 3) + \"...\"\n",
    "            } else {\n",
    "              str\n",
    "            }\n",
    "          }: Seq[String]\n",
    "        }\n",
    "    publish.html(s\"\"\" <table>\n",
    "                <tr>\n",
    "                 ${header.map(h => s\"<th>${escape(h)}</th>\").mkString}\n",
    "                </tr>\n",
    "                ${rows.map {row =>\n",
    "                  s\"<tr>${row.map{c => s\"<td>${escape(c)}</td>\" }.mkString}</tr>\"\n",
    "                }.mkString}\n",
    "            </table>\n",
    "        \"\"\")\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[36mcomposer\u001b[39m: \u001b[32mDataFrame\u001b[39m = [comp_no: int, comp_is: int ... 1 more field]\n",
       "\u001b[36mcomposition\u001b[39m: \u001b[32mDataFrame\u001b[39m = [c_no: int, comp_date: string ... 2 more fields]\n",
       "\u001b[36mconcert\u001b[39m: \u001b[32mDataFrame\u001b[39m = [concert_no: int, concert_venue: string ... 3 more fields]\n",
       "\u001b[36mhas_composed\u001b[39m: \u001b[32mDataFrame\u001b[39m = [cmpr_no: int, cmpn_no: int]\n",
       "\u001b[36mmusician\u001b[39m: \u001b[32mDataFrame\u001b[39m = [m_no: int, m_name: string ... 4 more fields]\n",
       "\u001b[36mperformance\u001b[39m: \u001b[32mDataFrame\u001b[39m = [pfrmnc_no: int, gave: int ... 3 more fields]\n",
       "\u001b[36mperformer\u001b[39m: \u001b[32mDataFrame\u001b[39m = [perf_no: int, perf_is: int ... 2 more fields]\n",
       "\u001b[36mplace\u001b[39m: \u001b[32mDataFrame\u001b[39m = [place_no: int, place_town: string ... 1 more field]\n",
       "\u001b[36mplays_in\u001b[39m: \u001b[32mDataFrame\u001b[39m = [player: int, band_id: int]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val composer = hiveCxt.table(\"sqlzoo.composer\")\n",
    "val composition = hiveCxt.table(\"sqlzoo.composition\")\n",
    "val concert = hiveCxt.table(\"sqlzoo.concert\")\n",
    "val has_composed = hiveCxt.table(\"sqlzoo.has_composed\")\n",
    "val musician = hiveCxt.table(\"sqlzoo.musician\")\n",
    "val performance = hiveCxt.table(\"sqlzoo.performance\")\n",
    "val performer = hiveCxt.table(\"sqlzoo.performer\")\n",
    "val place = hiveCxt.table(\"sqlzoo.place\")\n",
    "val plays_in = hiveCxt.table(\"sqlzoo.plays_in\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the organiser\"s name of the concert in the Assembly Rooms after the first of Feb, 1997.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>m_name</th>\n",
       "                </tr>\n",
       "                <tr><td>James Steeple</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "(concert.filter((concert(\"concert_venue\") === \"Assembly Rooms\") && \n",
    "                (concert(\"con_date\")>\"1997-02-01\"))\n",
    " .join(musician, (concert(\"concert_orgniser\") === musician(\"m_no\")))\n",
    " .select(\"m_name\")\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**Find all the performers who played guitar or violin and were born in England.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>m_name</th>\n",
       "                </tr>\n",
       "                <tr><td>Alan Fluff</td></tr><tr><td>Davis Heavan</td></tr><tr><td>Harriet Smithson</td></tr><tr><td>Harry Forte</td></tr><tr><td>James First</td></tr><tr><td>Theo Mengel</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "(performer.filter(performer(\"instrument\").isin(List(\"violin\", \"guitar\"): _*))\n",
    " .join(musician, (performer(\"perf_is\") === musician(\"m_no\")))\n",
    " .join(place.filter(place(\"place_country\") === \"England\"),\n",
    "       (musician(\"born_in\") === place(\"place_no\")))\n",
    " .select(\"m_name\")\n",
    " .orderBy(\"m_name\")\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the names of musicians who have conducted concerts in USA together with the towns and dates of these concerts.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>m_name</th><th>place_town</th><th>con_date</th>\n",
       "                </tr>\n",
       "                <tr><td>James Steeple</td><td>New York</td><td>1997-06-15</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "(concert.join(place.filter(place(\"place_country\") === \"USA\"),\n",
    "              (concert(\"concert_in\") === place(\"place_no\")))\n",
    " .join(performance, (concert(\"concert_no\") === performance(\"performed_in\")))\n",
    " .join(musician, (performance(\"conducted_by\") === musician(\"m_no\")))\n",
    " .select(\"m_name\", \"place_town\", \"con_date\")\n",
    " .distinct()\n",
    " .orderBy(\"m_name\")\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**How many concerts have featured at least one composition by Andy Jones? List concert date, venue and the composition's title.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>con_date</th><th>concert_venue</th><th>c_title</th>\n",
       "                </tr>\n",
       "                <tr><td>1996-05-08</td><td>Bridgewater Hall</td><td>A Simple Piece</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "(concert.join(composition, (concert(\"concert_no\") === composition(\"c_in\")))\n",
    " .join(has_composed, (composition(\"c_no\") === has_composed(\"cmpn_no\")))\n",
    " .join(composer, (has_composed(\"cmpr_no\") === composer(\"comp_no\")))\n",
    " .join(musician.filter(musician(\"m_name\") === \"Andy Jones\"), \n",
    "       (composer(\"comp_is\") === musician(\"m_no\")))\n",
    " .select(\"con_date\", \"concert_venue\", \"c_title\")\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**List the different instruments played by the musicians and avg number of musicians who play the instrument.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       " <table>\n",
       "                <tr>\n",
       "                 <th>instrument</th><th>Alan Fluff</th><th>Davis Heavan</th><th>Elsie James</th><th>Harriet Smithson</th><th>Harry Forte</th><th>Helen Smyth</th><th>James First</th><th>James Quick</th><th>Jeff Dawn</th><th>John Smith</th><th>Louise Simpson</th><th>Sue Little</th><th>Theo Mengel</th><th>ALL</th>\n",
       "                </tr>\n",
       "                <tr><td>ALL</td><td>2</td><td>3</td><td>3</td><td>2</td><td>3</td><td>1</td><td>1</td><td>2</td><td>2</td><td>3</td><td>3</td><td>1</td><td>3</td><td>29</td></tr><tr><td>banjo</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>1</td></tr><tr><td>bass</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>1</td><td>1</td><td>0</td><td>0</td><td>3</td></tr><tr><td>cello</td><td>0</td><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>3</td></tr><tr><td>clarinet</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td></tr><tr><td>cornet</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>0</td><td>1</td></tr><tr><td>drums</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>2</td></tr><tr><td>flute</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>2</td></tr><tr><td>guitar</td><td>0</td><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>2</td></tr><tr><td>horn</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td></tr><tr><td>trombone</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>0</td><td>1</td></tr><tr><td>trumpet</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td></tr><tr><td>viola</td><td>1</td><td>1</td><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>4</td></tr><tr><td>violin</td><td>1</td><td>0</td><td>0</td><td>1</td><td>1</td><td>0</td><td>1</td><td>0</td><td>1</td><td>1</td><td>0</td><td>0</td><td>1</td><td>7</td></tr>\n",
       "            </table>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "\u001b[36mt\u001b[39m: \u001b[32mDataFrame\u001b[39m = [perf_no: int, perf_is: int ... 8 more fields]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val t = (performer.join(musician,\n",
    "                        (performer(\"perf_is\") === musician(\"m_no\"))))\n",
    "(t.groupBy(\"instrument\")\n",
    " .pivot(\"m_name\")\n",
    " .agg(count(\"m_no\"))\n",
    " .union(t.withColumn(\"instrument\", lit(\"ALL\"))\n",
    "        .groupBy(\"instrument\")\n",
    "        .pivot(\"m_name\")\n",
    "        .agg(count(\"m_no\")))\n",
    " .join(t.groupBy(\"instrument\")\n",
    "       .agg(count(\"m_no\").alias(\"ALL\"))\n",
    "       .union(t.withColumn(\"instrument\", lit(\"ALL\"))\n",
    "              .groupBy(\"instrument\")\n",
    "              .agg(count(\"m_no\").alias(\"ALL\"))),\n",
    "       \"instrument\")\n",
    " .orderBy(\"instrument\")\n",
    " .na.fill(0)\n",
    " .showHTML())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Scala",
   "language": "scala",
   "name": "scala"
  },
  "language_info": {
   "codemirror_mode": "text/x-scala",
   "file_extension": ".sc",
   "mimetype": "text/x-scala",
   "name": "scala",
   "nbconvert_exporter": "script",
   "version": "2.12.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
